﻿'ayoub
Imports System.Data
Imports System.Data.SqlClient
Public Class Frm_list_location
    Public str As String = ""
    Dim row As DataRow
    Public Sub remplirDataset()
        '-----------------------------------------------------------
        Try
            dse.Tables("Matériel").Rows.Clear()
            Cmd = New SqlCommand("select Nom_Equipement,[Type],Clients_Demande_Loc.Quantite,Prix " &
                                 "from Clients_Demande_Loc,Equipement " &
                                 "where Clients_Demande_Loc.Id_Equipement=Equipement.Id_Equipement " &
                                 "and N_demande=" & CInt(List_tous.Items(List_tous.SelectedIndices(0)).SubItems(7).Text), Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Do While Dr.Read()
                    row = dse.Tables("Matériel").NewRow()
                    row("Désignation") = Dr("Nom_Equipement") & "(" & Dr("Type") & ")"
                    row("Nbr") = Dr("Quantite")
                    row("Prix Unitaire") = Dr("Prix")
                    row("Total") = Dr("Quantite") * Dr("Prix")
                    dse.Tables("Matériel").Rows.Add(row)
                Loop
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        '------------------------------------------------------------
        Try
            dse.Tables("Info Loc").Rows.Clear()
            Cmd = New SqlCommand("select nom,prenom,Nom_entreprise,Portable,Type_Client," &
                                 "Date_Sortie,Date_entrer " &
                                 "from clients,Clients_Demande_Loc " &
                                 "where Clients.Id_Client=Clients_Demande_Loc.Id_Client " &
                                 "and N_demande=" & CInt(List_tous.Items(List_tous.SelectedIndices(0)).SubItems(7).Text), Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Dr.Read()
                row = dse.Tables("Info Loc").NewRow()
                If Dr("Type_Client") = "Entreprise" Then
                    row("Client") = Dr("Nom_entreprise")
                Else
                    row("Client") = Dr("nom") & " " & Dr("prenom")
                End If
                row("Portable") = Dr("Portable")
                row("Date Sortie") = Format(Dr("Date_Sortie"), "dd/MM/yyyy")
                row("Date Entrer") = Format(Dr("Date_entrer"), "dd/MM/yyyy")
                dse.Tables("Info Loc").Rows.Add(row)
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        '-------------------------------------------------
        Try
            dse.Tables("Paiement Loc").Rows.Clear()
            Cmd = New SqlCommand("select sum(Montant) as Montant,Remis from Clients_Paiement_loc " &
                                 "where N_demande=" & CInt(List_tous.Items(List_tous.SelectedIndices(0)).SubItems(7).Text) &
                                 " and type_paiement='Requis' " &
                                 "group by Remis ", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Dr.Read()
                row = dse.Tables("Paiement Loc").NewRow()
                row("Montant") = Dr("Montant")
                row("Remis") = Dr("Remis")

            End If
            Dr.Close()
            cnClose()
            '----------------------------------------------
            Cmd = New SqlCommand("select sum(Montant) as Avance from Clients_Paiement_loc " &
                                 "where N_demande=" & CInt(List_tous.Items(List_tous.SelectedIndices(0)).SubItems(7).Text) &
                                 " and type_paiement='Paye'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Dr.Read()
                row("Avance") = Dr("Avance")
                row("Reste") = row("Montant") - row("Remis") - Dr("Avance")
                dse.Tables("Paiement Loc").Rows.Add(row)
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub remplir()
        List_tous.Items.Clear()
        If Check_en_cours.Checked = True Then
            str = "select * from Clients_Demande_Loc,Clients where Clients.Id_Client=Clients_Demande_Loc.Id_Client and recupere='non'"
        Else
            str = "select * from Clients_Demande_Loc,Clients where Clients.Id_Client=Clients_Demande_Loc.Id_Client"
        End If
        Try
            Cmd = New SqlCommand(Str, Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Do While Dr.Read()
                    If Dr("Type_Client") = "Entreprise" Then
                        Dim Line As ListViewItem = New ListViewItem(New String() {Dr("N_demande"), Dr("Nom_entreprise"), Dr("Nom_Equipement"), (Format(Dr("Date_Sortie"), "yyyy-MM-dd")), (Format(Dr("Date_entrer"), "yyyy-MM-dd")), Dr("Qui_Donné")})
                        List_tous.Items.Add(Line)
                    Else
                        Dim Line As ListViewItem = New ListViewItem(New String() {Dr("N_demande"), Dr("Nom") & "  " & Dr("Prenom"), (Format(Dr("Date_Sortie"), "yyyy-MM-dd")), (Format(Dr("Date_entrer"), "yyyy-MM-dd")), Dr("Qui_Donné")})
                        List_tous.Items.Add(Line)
                    End If

                Loop
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        txt_rech.Clear()
    End Sub
    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub comb_selectionnez_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles comb_selectionnez.KeyPress
        e.Handled = True
    End Sub

    Private Sub Frm_list_location_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        comb_selectionnez.SelectedIndex = 0
        remplir()
    End Sub

    Private Sub Check_en_cours_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Check_en_cours.CheckedChanged
        remplir()
    End Sub

    Private Sub txt_rech_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_rech.TextChanged
        Dim mot As String = ""
        If comb_selectionnez.SelectedItem = "Nom" Then
            mot = "Nom"
        ElseIf comb_selectionnez.SelectedItem = "Prenom" Then
            mot = "Prenom"
        ElseIf comb_selectionnez.SelectedItem = "Cin" Then
            mot = "Cin"
        ElseIf comb_selectionnez.SelectedItem = "Nom de L'entreprise" Then
            mot = "Nom_entreprise"

        ElseIf comb_selectionnez.SelectedItem = "Nom de L'interlocuteur" Then
            mot = "Nom_interlocuteur"
        ElseIf comb_selectionnez.SelectedItem = "Date de Sortie" Then
            mot = "Date_Sortie"
        ElseIf comb_selectionnez.SelectedItem = "Date D'entrer" Then
            mot = "Date_entrer"
        End If

        List_tous.Items.Clear()
        If Check_en_cours.Checked = True Then
            str = "select * from Clients_Demande_Loc,Clients where Clients.Id_Client=Clients_Demande_Loc.Id_Client and recupere='non' and " & mot & " like '%" & txt_rech.Text & "%'"
        Else
            str = "select * from Clients_Demande_Loc,Clients where Clients.Id_Client=Clients_Demande_Loc.Id_Client and " & mot & " like '%" & txt_rech.Text & "%'"
        End If

        Try
            Cmd = New SqlCommand(str, Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Do While Dr.Read()
                    If Dr("Type_Client") = "Entreprise" Then
                        Dim Line As ListViewItem = New ListViewItem(New String() {Dr("N_demande"), Dr("Nom_entreprise"), Dr("Nom_Equipement"), (Format(Dr("Date_Sortie"), "yyyy-MM-dd")), (Format(Dr("Date_entrer"), "yyyy-MM-dd")), Dr("Qui_Donné")})
                        List_tous.Items.Add(Line)
                    Else
                        Dim Line As ListViewItem = New ListViewItem(New String() {Dr("N_demande"), Dr("Nom") & "  " & Dr("Prenom"), (Format(Dr("Date_Sortie"), "yyyy-MM-dd")), (Format(Dr("Date_entrer"), "yyyy-MM-dd")), Dr("Qui_Donné")})
                        List_tous.Items.Add(Line)
                    End If
                Loop
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        If txt_rech.Text = "" Then
            remplir()
        End If
    End Sub

    Private Sub List_tous_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles List_tous.DoubleClick
        Frm_Demande_Clients_loc.MdiParent = Frm_Main
        Frm_Demande_Clients_loc.Show()
        Dim n As String = ""
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    ' Frm_Demande_Clients_loc.txt_n_demande.Text = .Items(.SelectedIndices(i)).SubItems(0).Text
                    'Frm_Demande_Clients_loc.Comb_client.SelectedItem = .Items(.SelectedIndices(i)).SubItems(1).Text
                    'Frm_Demande_Clients_loc.Date_sortie.Value = .Items(.SelectedIndices(i)).SubItems(2).Text
                    'Frm_Demande_Clients_loc.Date_entrer.Value = .Items(.SelectedIndices(i)).SubItems(3).Text
                    ' Frm_Demande_Clients_loc.txt_donné.Text = .Items(.SelectedIndices(i)).SubItems(4).Text
                    n = .Items(.SelectedIndices(i)).SubItems(0).Text
                Next
            End If
        End With
        Try
            Cmd = New SqlCommand("select * from Clients_Equi_Loc,Equipement where Clients_Equi_Loc.Id_Equipement=Equipement.Id_Equipement and N_demande=" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Do While Dr.Read()
                    Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Nom_Equipement"), Dr("Type"), Dr("Quantite"), Dr("Prix"), Dr("Remarque"), Dr("Id_Equipement")})
                    List_tous.Items.Add(Line)
                Loop
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub List_tous_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged

    End Sub

    Private Sub comb_selectionnez_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles comb_selectionnez.SelectedIndexChanged
        txt_rech.Clear()
    End Sub

    Private Sub btn_bon_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_bon.Click
        Try
            If List_tous.SelectedItems.Count > 0 Then
                remplirDataset()
                id_loc = CInt(List_tous.Items(List_tous.SelectedIndices(0)).SubItems(7).Text)
                bon = "liste loc"
                frm_bon.MdiParent = Frm_Main
                frm_bon.Show()
                Me.Close()
            Else

            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
End Class